/*****************************************************************************
Author: Zhang Jianwen

Date: 2000/3/31

Module Name: DbAdapter.java

DBMS: Microsoft SQL Server v7.0

Previous Module: none

Next Module: any modules want to access database

Module Function Description:
    This class packages basic database access methods
    Interface methods list:
        Constructor:
            DbAdapter() and DbAdapter(false)
            - used to create a dbadapter without connection pool

            DbAdapter(true)
            - used to create a dbadapter with connection pool

        Methods:
            void executeQuery(String sqlstr)
            - used to execute sql query string

            void executeUpdate(String sqlstr)
            - used to execute sql update string

            boolean next()
            - used to execute ResultSet.next()

            void close()
            - used to close dbadapter

            int getInt(int nField) and int getInt(String nField)
            - used to getInt

            String getString(int nField) and String getString(String nField)
            - used to getString

            float getFloat(int nField) and float getFloat(String nField)
            - used to getFloat

*******************************************************************************/

package linuxbox.util;

import java.io.*;
import java.math.*;
import java.sql.*;
import java.sql.Date;
import java.util.*;
import javax.naming.*;

import org.apache.log4j.Logger;

import linuxbox.exception.*;
import linuxbox.BoxStatic;
import linuxbox.common.BoxInit;

public class DbAdapter {
    protected static Logger logger = Logger.getLogger(DbAdapter.class.getName());

    private Connection _con;
    private Statement _stmt;
    private ResultSet _rs;
    private String _strSql;
    private boolean isPool = false;
    private static boolean bFirstTime = true;

    //private static ConnectionPool pool = null;
    private static String db_driver = null;
    private static String db_url = null;
    private static String username = null;
    private static String password = null;
    private static String jndi_url = null;
    private static String db_version = null;
    private static Properties db_props = null;

    public DbAdapter() throws DbBeanException {
        init(false);
    }

    public DbAdapter(boolean poolOrNot) throws DbBeanException {
        init(poolOrNot);
    }

    protected void init(boolean poolOrNot) throws DbBeanException {
        this.isPool = poolOrNot;
        makeconn();
    }

    private void makeconn() throws DbBeanException {
        if (logger.isDebugEnabled())
          logger.debug("Making connection: " + bFirstTime + ", " + isPool);
        if (bFirstTime) {
            username = BoxStatic.username;
            password = BoxStatic.password;
            jndi_url = "jdbc/sample";
            //db_url = "jdbc:odbc:linuxbox";
            //db_driver = "sun.jdbc.odbc.JdbcOdbcDriver";
            db_url = BoxStatic.dbUrl;
            db_driver = BoxStatic.dbDriver;

            db_props = new Properties();
            //log("username is " + BoxStatic.username);
            //log("password is " + BoxStatic.password);
            db_props.put("user", username);
            db_props.put("password", password);

            bFirstTime = false;
        }

        if(isPool == false) {
            // Connect to the database. To connect to a remote database,
            // insert the connect string after the @ sign in the connection URL.
            try {
                Class.forName(db_driver).newInstance();
            } catch(Exception e) {
                throw new DbBeanException("Driver Create Error: " + e.getMessage());
            }

            try {
                _con = DriverManager.getConnection(db_url, db_props);
                _stmt = _con.createStatement();
                _stmt.setQueryTimeout(30);
                _rs = null;

                //log(_con.toString());
                //log(_stmt.toString());
            } catch(Exception e) {
                throw new DbBeanException("Conn & Stmt Create Error: " + e.getMessage());
            }
        } else {
            try {
                _con = BoxStatic.cPool.getConnection();
                _stmt = (Statement)_con.createStatement();
                _stmt.setQueryTimeout(30);
                _rs = null;
            } catch (Exception e) {
                e.printStackTrace();
                throw new DbBeanException("Conn & Stmt Create Error: " + e.getMessage());
            }
        }
    }

    public void close() throws DbBeanException {
        try {
            if (_rs != null) {
                _rs.close();
                _rs = null;
            }
            
            if(_stmt != null) {
                _stmt.close();
                _stmt = null;
            }
            
            if(_con != null) {
                if(isPool == false) {
                    _con.close();
                    _con = null;
                } else {
                    BoxStatic.cPool.releaseConnection(_con);
                }
            }
        } catch (Exception e) {
            throw new DbBeanException("Adapter Close Error: " + e.getMessage());
        }
    }

    public boolean isGood() {
        if (_con != null && _stmt != null) {
            return true;
        } else {
            return false;
        }
    }

    public  void setQueryTimeout(int nSeconds) throws DbBeanException {
        try {
            _stmt.setQueryTimeout(nSeconds);
        } catch(Exception e) {
            throw new DbBeanException("setQueryTimeout Error: " + e.getMessage());
        }
    }

    public void setAutoCommit(boolean bl) throws DbBeanException {
        try {
            _con.setAutoCommit(bl);
        } catch(Exception e) {
            throw new DbBeanException("setAutoCommit Error: " + e.getMessage());
        }
    }

    public void commit() throws DbBeanException {
        try {
            _con.commit();
        } catch(Exception e) {
            throw new DbBeanException("commit Error: " + e.getMessage());
        }
    }

    public void rollBack() throws DbBeanException {
        try {
            _con.rollback();
        } catch(Exception e) {
            throw new DbBeanException("rollBack Error: " + e.getMessage());
        }
    }

    public int executeUpdate(String strSql) throws DbBeanException {
        try {
            if (_rs != null)
                _rs.close();
            _rs = null;
            logger.debug("executeUpdate:" + strSql);
            return _stmt.executeUpdate(strSql);
        } catch (SQLException e) {
            throw new DbBeanException("executeUpdate Error: \n" + strSql
                + "\n" + e.getMessage());
        }
    }

    public void executeQuery(String strSql) throws DbBeanException {
        try {
            _strSql = strSql;

            if (_rs != null)
                _rs.close();

            logger.debug("executeQuery:" + strSql);
            _rs = _stmt.executeQuery(strSql);
        } catch (SQLException e) {
            throw new DbBeanException("executeQuery Error: \n" + strSql
                + "\n" + e.getMessage());
        }
    }

    public void executeProcedure(String strSql) throws DbBeanException {
        try {
            _strSql = strSql;

            if (_rs != null)
                _rs.close();

            _rs = _stmt.executeQuery(strSql);
        } catch (SQLException e) {
            throw new DbBeanException("executeQuery Error: " + strSql + e.getMessage());
        }
    }

    public boolean next() throws DbBeanException {
        try {
            return _rs.next();
        } catch (SQLException e) {
            //System.out.println("next Error: " + _strSql + e.getMessage());
            throw new DbBeanException("next Error: " + _strSql + e.getMessage());
        }
    }

    public int getInt(int nField) throws DbBeanException {
        try {
            return _rs.getInt(nField);
        } catch (SQLException e) {
            //System.out.println("getInt Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getInt Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public int getInt(String nField) throws DbBeanException {
        try {
            return _rs.getInt(nField);
        } catch (SQLException e) {
            //System.out.println("getInt Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getInt Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public long getLong(int nField) throws DbBeanException {
        try {
            return _rs.getLong(nField);
        } catch (SQLException e) {
            //System.out.println("getLong Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getLong Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public long getLong(String nField) throws DbBeanException {
        try {
            return _rs.getLong(nField);
        } catch (SQLException e) {
            //System.out.println("getLong Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getLong Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public byte getByte(int nField) throws DbBeanException {
        try {
            return _rs.getByte(nField);
        } catch (SQLException e) {
            //System.out.println("getByte Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getByte Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public byte getByte(String nField) throws DbBeanException {
        try {
            return _rs.getByte(nField);
        } catch (SQLException e) {
            //System.out.println("getByte Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getByte Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public byte[] getBytes(int nField) throws DbBeanException {
        try {
            return _rs.getBytes(nField);
        } catch (SQLException e) {
            //System.out.println("getBytes Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getBytes Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public byte[] getBytes(String nField) throws DbBeanException {
        try {
            return _rs.getBytes(nField);
        } catch (SQLException e) {
            //System.out.println("getBytes Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getBytes Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public Date getDate(int nField) throws DbBeanException {
        try {
            return _rs.getDate(nField);
        } catch (SQLException e) {
            //System.out.println("getDate Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getDate Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public Date getDate(String nField) throws DbBeanException {
        try {
            return _rs.getDate(nField);
        } catch (SQLException e) {
            //System.out.println("getDate Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getDate Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public Time getTime(int nField) throws DbBeanException {
        try {
            return _rs.getTime(nField);
        } catch (SQLException e) {
            //System.out.println("getTime Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getTime Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public Time getTime(String nField) throws DbBeanException {
        try {
            return _rs.getTime(nField);
        } catch (SQLException e) {
            //System.out.println("getTime Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getTime Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public Timestamp getTimestamp(int nField) throws DbBeanException {
        try {
            return _rs.getTimestamp(nField);
        } catch (SQLException e) {
            throw new DbBeanException("getTimestamp Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public Timestamp getTimestamp(String nField) throws DbBeanException {
        try {
            return _rs.getTimestamp(nField);
        } catch (SQLException e) {
            //System.out.println("getTime Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getTime Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public float getFloat(int nField) throws DbBeanException {
        try {
            return _rs.getFloat(nField);
        } catch (SQLException e) {
            //System.out.println("getFloat Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getFloat Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public float getFloat(String nField) throws DbBeanException {
        try {
            return _rs.getFloat(nField);
        } catch (SQLException e) {
            //System.out.println("getFloat Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getFloat Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public double getDouble(int nField) throws DbBeanException {
        try {
            return _rs.getDouble(nField);
        } catch (SQLException e) {
            //System.out.println("getDouble Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getDouble Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public double getDouble(String nField) throws DbBeanException {
        try {
            return _rs.getDouble(nField);
        } catch (SQLException e) {
            //System.out.println("getDouble Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getDouble Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public String getString(int nField) throws DbBeanException {
        try {
            String s = _rs.getString(nField);
            if (s == null)
                return "";
            return s.trim();
        } catch (SQLException e) {
            //System.out.println("getString Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getString Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    public String getString(String nField) throws DbBeanException {
        try {
            String s = _rs.getString(nField);
            if (s == null)
                return "";
            return s.trim();
        } catch (SQLException e) {
            //System.out.println("getString Error: " + nField + ": "
            //  + _strSql + e.getMessage());
            throw new DbBeanException("getString Error: " + nField + ": "
                + _strSql + e.getMessage());
        }
    }

    /*
    public String getVarchar(int nFromLanguage, int nToLanguage, int nField)
    throws DbBeanException  {
    try {
        String str = _rs.getString(nField);
        if (str != null) {
            byte[] ab = str.getBytes("ISO-8859-1");
            return Translator.getInstance().translate(ab, nFromLanguage, nToLanguage);
        }
    } catch (UnsupportedEncodingException e) {
        System.err.println(e + ": getBytes: " + nField + ": " + _strSql);
    } catch (SQLException e) {
        System.err.println(e + ": getBytes: " + nField + ": " + _strSql);
        throw e;
    }

        return "";
    }*/

    /*
    public String getText(int nFromLanguage, int nToLanguage, int nField) throws SQLException {
        try {
            byte[] ab = null;
            int nLen = _rs.getInt(nField);
            if (nLen != 0) {
                ab = new byte[nLen];
                InputStream is = _rs.getBinaryStream(nField+1);
                is.read(ab);
                is.close();
            }

            if (ab != null)
            return Translator.getInstance().translate(ab, nFromLanguage, nToLanguage);
        } catch (IOException e) {
            System.err.println(e + ": getInt: " + nField + "getBinaryStream: " + (nField+1) + ": " + _strSql);
        } catch (SQLException e) {
            System.err.println(e + ": getInt: " + nField + "getBinaryStream: " + (nField+1) + ": " + _strSql);
            throw e;
        }
        return "";
    }*/

    public byte[] getImage(int nField) throws DbBeanException {
        byte[] ab = null;
        try {
            int nLen = _rs.getInt(nField);
            if (nLen != 0) {
                ab = new byte[nLen];
                InputStream is = _rs.getBinaryStream(nField+1);
                is.read(ab);
                is.close();
            }
        } catch (IOException e) {
            throw new DbBeanException("getImage Error: " + nField + "getBinaryStream: "
                + (nField+1) + ": " + _strSql + e.getMessage());
        } catch (SQLException e) {
            throw new DbBeanException("getImage Error: " + nField + "getBinaryStream: "
                + (nField+1) + ": " + _strSql + e.getMessage());
        }

        return ab;
    }

    /*
    public BigDecimal getBigDecimal(int n) throws DbBeanException {
        try {
            return _rs.getBigDecimal(n);
        } catch (SQLException e) {
            System.err.println("getBigDecimal Error: " + n + ": " + _strSql
            + e.getMessage());
            throw new DbBeanException("getBigDecimal Error: " + n + ": " + _strSql
            + e.getMessage());
        }
    }*/

    /*
    public BigDecimal getBigDecimal(String n) throws DbBeanException {
        try {
            return _rs.getBigDecimal(n);
        } catch (SQLException e) {
            System.err.println("getBigDecimal Error: " + n + ": " + _strSql
            + e.getMessage());
            throw new DbBeanException("getBigDecimal Error: " + n + ": " + _strSql
            + e.getMessage());
        }
    }*/

    /*
    public BigDecimal getBigDecimal(int n, int ns) throws DbBeanException {
        try {
            return _rs.getBigDecimal(n, ns);
        } catch (SQLException e) {
            System.err.println("getBigDecimal Error: " + n + ": " + _strSql
            + e.getMessage());
            throw new DbBeanException("getBigDecimal Error: " + n + ": " + _strSql
            + e.getMessage());
        }
    }*/

    public Object getObject(int n) throws DbBeanException {
        try {
            return _rs.getObject(n);
        } catch (SQLException e) {
            //System.out.println("getObject Error: " + n + ": " + _strSql
            //  + e.getMessage());
            throw new DbBeanException("getObject Error: " + n + ": " + _strSql
                + e.getMessage());
        }
    }


    /*
    public int getInt(String strSql) throws DbBeanException {
        try {
            executeQuery(strSql);
            if (_rs.next()) return _rs.getInt(1);
        } catch (SQLException e) {
            System.err.println("getInt Error: " + n + ": " + _strSql
            + e.getMessage());
            throw new DbBeanException("getInt Error: " + n + ": " + _strSql
            + e.getMessage());
        }

        return 0;
    }*/

    /*
    public String getString(String strSql) throws SQLException {
        try {
            executeQuery(strSql);
            if (_rs.next()) {
                String s = _rs.getString(1);
                if (s != null)
                return s.trim();
            }
        } catch (SQLException e) {
            System.err.println(e + ": getString: " + strSql);
            throw e;
        }

        return "";
    }*/

    /*public String getVarchar(int nToLanguage, String strSql) throws SQLException {
    try {
    executeQuery(strSql);
    if (_rs.next()) {
    int nFromLanguage = _rs.getInt(1);
    byte[] ab = _rs.getBytes(2);
    if (ab != null) {
    return Translator.getInstance().translate(ab, nFromLanguage, nToLanguage);
    }
    }
    //} catch (UnsupportedEncodingException e) {
    //  System.err.println(e + ": getVarchar: " + strSql);
    //} catch (SQLException e) {
    System.err.println(e + ": getVarchar: " + strSql);
    throw e;
    }

    return "";
    }*/

    /*public byte[] getImage(String strSql) throws SQLException {
    byte[] ab = null;
    try {
    executeQuery(strSql);
    if (_rs.next()) {
    int nLen = _rs.getInt(1);
    ab = new byte[nLen];
    InputStream is = _rs.getBinaryStream(2);
    is.read(ab);
    is.close();
    }
    } catch (IOException e) {
    System.err.println(e + ": getImage: " + strSql);
    } catch (SQLException e) {
    System.err.println(e + ": getImage: " + strSql);
    throw e;
    }

    return ab;
    }*/

    /*public BigDecimal getBigDecimal(String strSql, int nScale) throws SQLException {
    try {
    executeQuery(strSql);
    if (_rs.next())
    return _rs.getBigDecimal(1, nScale);
    } catch (SQLException e) {
    System.err.println(e + ": getBigDecimal: " + strSql);
    throw e;
    }

    return null;
    }*/

    /*public Object getObject(String strSql) throws SQLException {
    try {
    executeQuery(strSql);
    if (_rs.next())
    return _rs.getObject(1);
    } catch (SQLException e) {
    System.err.println(e + ": getObject: " + strSql);
    throw e;
    }

    return null;
    }*/

    public static void main(String[] args) {
        try {
            //BoxInit.initTestEnv();
        } catch(Exception e) {
            e.printStackTrace();
        }

        DbAdapter db_bean = null;

        try {
            for (int i = 0; i < 20000; i++) {
                logger.debug("do it again ... ");
                getAndRelease();
            }
            BoxStatic.cPool.cleanAll();
        } catch(Exception e) {
            e.printStackTrace();
        }

        /*try {
            db_bean = new DbAdapter(true);
        } catch(DbBeanException e) {
            System.out.println("driver error: " + e.getMessage());
        }*/

        //try {
            /*db_bean.executeQuery("select loginid, firstname from usercontrol");

            while(db_bean.next()) {
                System.out.println("id is: "
                    + db_bean.getString("loginid") + " and name is: "
                    + db_bean.getString("firstname"));
            }
            System.out.println("....");
            System.out.println(" ");*/

            /*db_bean.executeUpdate("insert into usercontrol (loginid, username, " +
                "password, userrights, userrights2, noexpire) values ('chenf', " +
                "'Feng Chen', 1, 1, 0, 1)");

            db_bean.executeQuery("select loginid, firstname from usercontrol");

            while(db_bean.next()) {
                System.out.println("id is: "
                    + db_bean.getString("loginid") + " and name is: "
                    + db_bean.getString("firstname"));
            }*/

            //db_bean.close();
        //} catch(Exception e) {
        //    System.out.println("Sql error: " + e.getMessage());
        //}
    }

    protected void test() throws Exception {
        executeQuery("select loginid, firstname from usercontrol where id=1");
        if (next()) {
            logger.debug("id is: " + getString("loginid") + " and name is: "
                + getString("firstname"));
        }
    }

    protected static void getAndRelease() throws Exception {
        BoxStatic.cPool.showUsage();
        DbAdapter da0 = new DbAdapter(true);
        BoxStatic.cPool.showUsage();
        DbAdapter da1 = new DbAdapter(true);
        BoxStatic.cPool.showUsage();
        DbAdapter da2 = new DbAdapter(true);
        BoxStatic.cPool.showUsage();
        DbAdapter da3 = new DbAdapter(true);
        BoxStatic.cPool.showUsage();
        DbAdapter da4 = new DbAdapter(true);
        BoxStatic.cPool.showUsage();

        da0.test();
        da1.test();
        da2.test();
        da3.test();
        da4.test();

        da0.close();
        BoxStatic.cPool.showUsage();
        da1.close();
        BoxStatic.cPool.showUsage();
        da2.close();
        BoxStatic.cPool.showUsage();
        da3.close();
        BoxStatic.cPool.showUsage();
        da4.close();
        BoxStatic.cPool.showUsage();
    }
}
